using System;
using System.Data;
using System.Text;

using Elev.Core.Data;
using Elev.Core.Utility;

namespace Elev.Core.DAL
{
    internal class ExamInternal : SqliteTable
    {
        /// <summary>
        /// Makes sure the given period exists in the Database
        /// </summary>
        bool periodExists(int PeriodID)
        {
            string SQL = "SELECT COUNT(ID) FROM Period WHERE ID = @ID";
            return (Convert.ToInt32(SqliteAdapter.Get().ExecuteScalar(SQL, "@ID", PeriodID)) > 0);
        }

        bool examTypeExists(int ExamTypeID)
        {
            string SQL = "SELECT COUNT(ID) FROM ExamType WHERE ID = @ID";
            return (Convert.ToInt32(SqliteAdapter.Get().ExecuteScalar(SQL, "@ID", ExamTypeID)) > 0);
        }

        /// <summary>
        /// Returns a DataTable containing all the exams in the database
        /// </summary>
        internal DataTable GetExams()
        {
            string SQL = "SELECT * FROM Exam";
            return SqliteAdapter.Get().Execute(SQL);
        }

        /// <summary>
        /// Returns a DataTable containing all the exams in the database for a given period
        /// </summary>
        internal DataTable GetExams(int PeriodID)
        {
            string SQL = "SELECT * FROM Exam WHERE PeriodID = @PeriodID";
            return SqliteAdapter.Get().Execute(SQL, "@PeriodID", PeriodID);
        }

        /// <summary>
        /// Returns a DataTable containing one given exam
        /// </summary>
        internal DataTable GetExam(int ID)
        {
            string SQL = "SELECT * FROM Exam WHERE ID = @ID";
            return SqliteAdapter.Get().Execute(SQL, "@ID", ID);
        }

        internal int Add(string Name, DateTime Date, int PeriodID, int ExamTypeID, int Coefficient, string Note)
        {
            if (Name == "")
                throw new ArgumentException("An exam name is required.");

            if (Date == DateTime.MinValue)
                throw new ArgumentException("An exam date is required.");

            if (!periodExists(PeriodID))
                throw new ArgumentException("A valid period is required.");

            if (!examTypeExists(ExamTypeID))
                throw new ArgumentException("A valid exam type is required.");

            string SQL = "INSERT INTO Exam(Name, Date, PeriodID, ExamTypeID, Coefficient, Note) " + 
                            "VALUES(@Name, @Date, @PeriodID, @ExamTypeID, @Coefficient, @Note)";
            
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@Name", Name, "@Date", DateTools.FormatDate(Date),
                "@PeriodID", PeriodID, "@ExamTypeID", ExamTypeID, "@Coefficient", Coefficient, "@Note", Note);

            return Convert.ToInt32(SqliteAdapter.Get().ExecuteScalar("SELECT MAX(ID) FROM Exam"));
        }

        internal void Update(int ID, string Name, DateTime Date, int PeriodID, int ExamTypeID, int Coefficient, string Note)
        {
            if (Name == "")
                throw new ArgumentException("An exam name is required.");

            if (Date == DateTime.MinValue)
                throw new ArgumentException("An exam date is required.");

            if (!periodExists(PeriodID))
                throw new ArgumentException("A valid period is required.");

            if (!examTypeExists(ExamTypeID))
                throw new ArgumentException("A valid exam type is required.");

            string SQL = "UPDATE Exam SET Name = @Name, Date = @Date, PeriodID = @PeriodID, ExamTypeID = @ExamTypeID, " + 
                            "Coefficient = @Coefficient, Note = @Note WHERE ID = @ID";
            
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@Name", Name, "@Date", DateTools.FormatDate(Date), "@PeriodID", 
                PeriodID, "@ExamTypeID", ExamTypeID, "@Coefficient", Coefficient, "@Note", Note, "@ID", ID);
        }

        internal void Delete(int ID)
        {
            string SQL = "DELETE FROM Exam WHERE ID = @ID";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@ID", ID);

            // Delete all the grades for this exam
            SQL = "DELETE FROM Grade WHERE ExamID = @ExamID";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@ExamID", ID);
        }

        #region SqliteTable Members

        public override void Create()
        {
            string SQL = "CREATE TABLE [Exam] (" +
                            " ID INTEGER DEFAULT '' NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE," +
                            " Name VARCHAR(100) DEFAULT '' NOT NULL," +
                            " Date VARCHAR(11) DEFAULT '' NOT NULL," +
                            " PeriodID INTEGER DEFAULT '' NOT NULL," +
                            " ExamTypeID INTEGER DEFAULT '' NOT NULL," +
                            " Coefficient INTEGER DEFAULT '1' NOT NULL," +
                            " Note TEXT)";

            SqliteAdapter.Get().ExecuteNonQuery(SQL);
        }

        public override void Drop()
        {
            string SQL = "DROP TABLE [Exam]";
            SqliteAdapter.Get().ExecuteNonQuery(SQL);
        }

        #endregion
    }
}
